# -*- coding: utf-8 -*-

# graphic-pppoe-client - Easy graphic networking manager
#
# Author: GoTLiuM InSPiRiT <gotlium@gmail.com>
#
# Copyright 2010, GoTLiuM InSPiRiT <gotlium@gmail.com>.
#
# This program is free software: you can redistribute it and/or modify it
# under the terms of the GNU General Public License version 2, as published
# by the Free Software Foundation.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranties of
# MERCHANTABILITY, SATISFACTORY QUALITY, or FITNESS FOR A PARTICULAR
# PURPOSE.  See the GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License along
# with this program.  If not, see <http://www.gnu.org/licenses/>.

import sqlite3
from string import find
from re import escape

class SQLite(object):
	__conn_id = None
	__cursor  = None
	__result = None
	__dbname = ''
	my_query = ''

	def __display_err(self, error=''):
		raise 'SQLiteLib', "Error message:\n%s\nSql: %s" % (error, self.my_query)

	def __init__(self, dbname = None):
		self.__dbname = dbname
		del dbname


	def connect(self):
		if self.__dbname == '':
			self.__display_err('DB filename not set')

		try:
			conn_id = sqlite3.connect(self.__dbname)
		except NameError, val:
			self.__display_err(val)

		if not conn_id:
			self.__display_err('Connection error!')
		else:
			self.__conn_id = conn_id
			self.__cursor  = conn_id.cursor()
			del conn_id

		return True

	def query(self, query='', commit=True):
		self.my_query = query

		try:
			self.__result = self.__cursor.execute(query)
			if commit: self.__conn_id.commit()
		except NameError, val:
			self.__display_err(val)

		return True

	def commit(self):
		self.__conn_id.commit()

	def rollback(self):
		self.__conn_id.rollback()

	def insert_id(self):
		return self.__cursor.lastrowid

	def result( self, i=0 ):
		if self.__result():
			self.query(self.my_query)
			obj = self.__cursor.fetchone()
		else:
			return False

		return obj[i]

	def fetch(self):
		result = []
		rows = self.__cursor.fetchall()
		for currow in rows:
			rowdict = {}
			for curcol in range(0,len(self.__cursor.description)):
				rowdict[self.__cursor.description[curcol][0]] = currow[curcol]
			result.append(rowdict)
		return result

	def __where(self, where={}):
		where_len = len(where)
		if where_len > 0:
			where = self.__escape(where)
			end_where = []
			for (key,value) in where.items():
				if find(str(value), '!') != -1:
					value = value.replace('!', '')
					end_where.append(" %s != '%s' " % (key,value))
				else:
					end_where.append(" %s = '%s' " % (key,value))
			return  'WHERE ' + ' AND '.join(end_where)
		return ''

	def __escape(self, array):
		for key in array.keys():
			array[key] = str(array[key]).replace("'", "\\'")
		return array

	def insert(self, table='', array = {}, exception = [], commit=True):
		if exception and len(exception) > 0:
			for key in exception:
				if key in array.keys():
					del array[key]

		if len(array) > 0 and table != "":
			array = self.__escape(array)
			keys = ', '.join(array.keys())
			vals = "' , '".join(array.values())
			self.query( \
			"INSERT INTO %s (%s) VALUES ('%s')" % \
					(table, keys, vals), commit)

	def delete(self, table='', where={}, commit=True):
		self.query("DELETE FROM %s %s" % \
			(table, self.__where(where)), commit)

	def update(self, table='', array={}, where={}, exception=[], commit=True):
		if exception and len(exception) > 0:
			for key in exception:
				if key in array.keys():
					del array[key]
		data = [];
		if len(array) > 0:
			array = self.__escape(array)
			for (key,value) in array.items():
				data.append(" %s='%s' " % (key,value))

			where = self.__where(where)
			data  = ', '.join(data)
			self.query("UPDATE %s SET %s %s" % \
						(table, data, where), commit)

	def select(self, table='', where = {}, limit=False, order=''):
		if limit: limit =  ' LIMIT %s ' % str(limit)
		else: limit = ''

		if order != "": order =  " ORDER BY " + order
		conv_where = self.__where(where)
		add_to_sql = conv_where+order+limit

		self.query("SELECT * FROM %s %s" % (table, add_to_sql), False)
		result = self.fetch()

		if limit:
			self.query( "SELECT COUNT(*) FROM %s " % \
						(table, conv_where), False)
			obj = self.__cursor.fetchone()
			num = obj[0]
		else:
			num = len(result)

		return {'result': result, 'num': num}

	def disconnect(self):
		if self.__conn_id:
			try: self.__conn_id.close()
			except: pass
